﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using PromotionApp.Model;
using PromotionApp.DAL;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Transactions;

using PromotionApp.Utility;
//using System.EnterpriseServices;

namespace PromotionApp.BLL
{
    public class TopicBLL
    {
        #region Private Variable

        private static TopicBLL _TopicBLL = null;

        #endregion

        #region Constructor

        private TopicBLL()
        {
        }

        #endregion

        #region Private Method

        private string GetDomainCondition(ref bool blnConditionFlag, string strDomainName)
        {
            string strCondition = string.Empty;

            if (strDomainName.Length > 0)
                strCondition = string.Format(" {0} b.[DomainName] = '{1}'", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), strDomainName);

            return strCondition;
        }

        private string GetCategoryCondition(ref bool blnConditionFlag, int categoryId)
        {
            string strCondition = string.Empty;

            if (categoryId > 0)
                strCondition = string.Format(" {0} a.[CategoryId] = '{1}'", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), categoryId);

            return strCondition;
        }

        private string SearchToppicSummaryCTESql(string strDomainName, int categoryId, string strStartDate, string strEndDate)
        {
            StringBuilder sb = new StringBuilder();
            bool blnConditionFlag = false;

            sb.Append(" with temp as");
            sb.Append(" (");
            sb.Append(" select	row_number() over(order by a.[PostedDate] desc) as [No]");
            sb.Append(" , a.[TopicId]");
            sb.Append(" , a.[CategoryId]");
            sb.Append(" , a.[TopicDescription]");
            sb.Append(" , a.[Message]");
            sb.Append(" , isnull(a.[ImagePath], '') as [ImagePath]");
            sb.Append(" , a.[StatusCode]");
            sb.Append(" , isnull(a.[UserName], '') as [UserName]");
            sb.Append(" , isnull(a.[Email], '') as [Email]");
            sb.Append(" , a.[PostedDate]");
            sb.Append(" from [dbo].[TxTopic] as a");
            sb.Append(" inner join [dbo].[MsCategory] as b on a.[CategoryId] = b.[CategoryId]");
            sb.Append(GetDomainCondition(ref blnConditionFlag, strDomainName));
            sb.Append(GetCategoryCondition(ref blnConditionFlag, categoryId));
            sb.AppendFormat(" {0} a.[PostedDate] between convert(datetime, '{1} 00:00:00', 103) and convert(datetime, '{2} 23:59:59', 103)", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), strStartDate, strEndDate);
            sb.Append(" )");

            return sb.ToString();
        }

        private string SearchTopicSummarySearchSql(string strCTEQuery, int pageSize, int pageNumber)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strCTEQuery);
            sb.Append(" select [No], [TopicId], [CategoryId], [TopicDescription], [Message], [ImagePath], [StatusCode], [UserName], [Email], [PostedDate]");
            sb.Append(" from temp");
            sb.AppendFormat(" where [No] between {0} and {1}", DynamicQueryHelper.GetStartNo(pageSize, pageNumber), DynamicQueryHelper.GetEndNo(pageSize, pageNumber));

            return sb.ToString();
        }

        private string SearchTopicSummaryCountSql(string strCTEQuery)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strCTEQuery);
            sb.Append(" select count(*) as [RowCount]");
            sb.Append(" from temp");

            return sb.ToString();
        }

        private string GetToppicSummaryCTESql(int categoryId, string strTopicStatus)
        {
            StringBuilder sb = new StringBuilder();            

            sb.Append(" with temp as");
            sb.Append(" (");
	        sb.Append(" select a.[TopicId]");
			sb.Append(" , Count(*) as [NumReplies]");
	        sb.Append(" from dbo.[TxTopic] as a");
		    sb.Append(" inner join dbo.[TxReply] as b on a.[TopicId] = b.[TopicId]");
	        sb.AppendFormat(" where a.[CategoryId] = {0}", categoryId);		
	        sb.Append(" group by a.[TopicId]");		
            sb.Append(" ), temp2 as");
            sb.Append(" (");
	        sb.Append(" select a.[TopicId]");
			sb.Append(" , Max(IsNull(b.[PostedDate], a.[PostedDate])) as [LastedPostedDate]");
	        sb.Append(" from dbo.[TxTopic] as a");
		    sb.Append(" left join dbo.[TxReply] as b on a.[TopicId] = b.[TopicId]");
	        sb.AppendFormat(" where a.[CategoryId] = {0}", categoryId);
	        sb.Append(" group by a.[TopicId]");
            sb.Append(" ), temp3 as");
            sb.Append(" (");
            sb.Append(" SELECT row_number() over(order by a.[TopicId] desc) as [No]");
	        sb.Append(" ,a.[TopicId]");            
            sb.Append(" ,a.[TopicDescription]");            
            sb.Append(" ,a.[UserName]");
            sb.Append(" ,a.[Email]");
            sb.Append(" ,a.[PostedDate]");
            sb.Append(" ,a.[TotalView]");
            sb.Append(" ,isnull(b.[NumReplies], 0) as [NumReplies]");
            sb.Append(" ,c.[LastedPostedDate]");
            sb.Append(" FROM [dbo].[TxTopic] as a");
            sb.Append(" left join temp as b on a.[TopicId] = b.TopicId");
            sb.Append(" inner join temp2 as c on a.[TopicId] = c.[TopicId]");
            sb.AppendFormat(" WHERE a.[StatusCode] = '{0}' and a.[CategoryId] = {1}", strTopicStatus, categoryId);
            sb.Append(" )");

            return sb.ToString();
        }

        private string GetTopicSummarySearchSql(string strCTEQuery, int pageSize, int pageNumber)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strCTEQuery);
            sb.Append(" select [No], [TopicId], [TopicDescription], [UserName], [Email], [PostedDate], [TotalView], [NumReplies], [LastedPostedDate]");
            sb.Append(" from temp3");
            sb.AppendFormat(" where [No] between {0} and {1}", DynamicQueryHelper.GetStartNo(pageSize, pageNumber), DynamicQueryHelper.GetEndNo(pageSize, pageNumber));

            return sb.ToString();
        }

        private string GetTopicSummaryCountSql(string strCTEQuery)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strCTEQuery);
            sb.Append(" select count(*) as [RowCount]");
            sb.Append(" from temp3");

            return sb.ToString();
        }

        #endregion

        #region Public Method

        public void DeleteTopic(int topicId, int userId)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
            option.Timeout = TimeSpan.FromSeconds(30);

            ModulatorActivity log = new ModulatorActivity();
            log.PosteType = "T";
            log.PostId = topicId;
            log.StatusCode = "D";
            log.UserId = userId;

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, option))
            {
                using (SqlConnection conn = TopicDAL.GetInstance().GetConnection())
                {
                    conn.Open();
                    TopicDAL.GetInstance().UpdateTopicStatus(topicId, "D", conn);
                    ModulatorActivityDAL.GetInstance().AddModulatorActivity(log, conn);
                    scope.Complete();
                }
            }
        }

        public void PendingTopic(int topicId, int userId)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
            option.Timeout = TimeSpan.FromSeconds(30);

            ModulatorActivity log = new ModulatorActivity();
            log.PosteType = "T";
            log.PostId = topicId;
            log.StatusCode = "P";
            log.UserId = userId;

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, option))
            {
                using (SqlConnection conn = TopicDAL.GetInstance().GetConnection())
                {
                    conn.Open();
                    TopicDAL.GetInstance().UpdateTopicStatus(topicId, "P", conn);
                    ModulatorActivityDAL.GetInstance().AddModulatorActivity(log, conn);
                    scope.Complete();
                }
            }            
        }

        public void EnableTopic(int topicId, int userId)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
            option.Timeout = TimeSpan.FromSeconds(30);

            ModulatorActivity log = new ModulatorActivity();
            log.PosteType = "T";
            log.PostId = topicId;
            log.StatusCode = "E";
            log.UserId = userId;

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, option))
            {
                using (SqlConnection conn = TopicDAL.GetInstance().GetConnection())
                {
                    conn.Open();
                    TopicDAL.GetInstance().UpdateTopicStatus(topicId, "E", conn);
                    ModulatorActivityDAL.GetInstance().AddModulatorActivity(log, conn);
                    scope.Complete();
                }
            }              
        }

        public List<TopicSummary> GetTopicSummary(TopicPagingArgs e)
        {
            string strCTESql = GetToppicSummaryCTESql(e.CategoryId, e.TopicStatus);
            string strSearchSql = GetTopicSummarySearchSql(strCTESql, e.PageSize, e.PageNumber);
            string strCountQuery = GetTopicSummaryCountSql(strCTESql);

            List<TopicSummary> list = new List<TopicSummary>();
            e.Total = TopicDAL.GetInstance().GetTotalRow(strCountQuery);
            DynamicQueryHelper.GetMaxPageNumber(strCountQuery, e);
            DataSet ds = TopicDAL.GetInstance().GetDataSetFromSql(strSearchSql);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    TopicSummary o = new TopicSummary();

                    o.TopicId = Convert.ToInt32(r["TopicId"]);                    
                    o.TopicDescription = r["TopicDescription"].ToString().Trim();                    
                    o.UserName = r["UserName"].ToString().Trim();
                    o.Email = r["Email"].ToString().Trim();
                    o.NumReplies = Convert.ToInt32(r["NumReplies"]);
                    o.NumViews = Convert.ToInt32(r["TotalView"]);                    
                    o.LastedPostedDate = Convert.ToDateTime(r["LastedPostedDate"]);
                    o.TopicPostedDate = Convert.ToDateTime(r["PostedDate"]);
                    list.Add(o);
                }
            }

            return list;
        }

        public List<Topic> SearchTopicSummary(SearchPagingArgs e)
        {
            string strCTESql = SearchToppicSummaryCTESql(e.DomainName, e.CategoryId, e.StartDate, e.EndDate);
            string strSearchSql = SearchTopicSummarySearchSql(strCTESql, e.PageSize, e.PageNumber);
            string strCountQuery = SearchTopicSummaryCountSql(strCTESql);

            List<Topic> list = new List<Topic>();
            e.Total = TopicDAL.GetInstance().GetTotalRow(strCountQuery);
            DynamicQueryHelper.GetMaxPageNumber(strCountQuery, e);
            DataSet ds = TopicDAL.GetInstance().GetDataSetFromSql(strSearchSql);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    Topic o = new Topic();

                    o.TopicId = Convert.ToInt32(r["TopicId"]);
                    o.CategoryId = Convert.ToInt32(r["CategoryId"]);
                    o.TopicDescription = r["TopicDescription"].ToString().Trim();
                    o.Message = r["Message"].ToString().Trim();
                    o.ImagePath = r["ImagePath"].ToString().Trim();
                    o.StatusCode = r["StatusCode"].ToString().Trim();
                    o.UserName = r["UserName"].ToString().Trim();
                    o.Email = r["Email"].ToString().Trim();
                    o.PostedDate = Convert.ToDateTime(r["PostedDate"]);
                    list.Add(o);
                }
            }

            return list;
        }

        public Topic GetTopicById(int topicId)
        {
            Topic topic = new Topic();
            DataSet ds = TopicDAL.GetInstance().GetTopicById(topicId);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                DataRow r = ds.Tables[0].Rows[0];

                topic.TopicId = Convert.ToInt32(r["TopicId"]);
                topic.CategoryId = Convert.ToInt32(r["CategoryId"]);
                topic.TopicDescription = r["TopicDescription"].ToString().Trim();
                topic.Message = r["Message"].ToString().Trim();
                topic.ImagePath = r["ImagePath"].ToString().Trim();
                topic.StatusCode = r["StatusCode"].ToString().Trim();
                topic.UserName = r["UserName"].ToString().Trim();
                topic.Email = r["Email"].ToString().Trim();
                topic.PostedDate = Convert.ToDateTime(r["PostedDate"]);
            }
            else
                throw new ApplicationException("Data not found.");

            return topic;
        }

        public void AddTopic(Topic topic)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
            option.Timeout = TimeSpan.FromSeconds(30);

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, option))
            {
                using (SqlConnection conn = TopicDAL.GetInstance().GetConnection())
                {
                    conn.Open();
                    TopicDAL.GetInstance().AddTopic(topic, conn);
                    CategoryLastedPostDAL.GetInstance().UpdateCategoryLastedPost(topic.CategoryId, conn);
                    scope.Complete();
                }
            }              
        }

        public static TopicBLL GetInstance()
        {
            if (_TopicBLL == null)
                _TopicBLL = new TopicBLL();

            return _TopicBLL;
        }

        #endregion
    }
}